MySQL Transactions এবং ACID Properties

MySQL Transactions ডেটাবেস অপারেশনগুলির একটি সেট যা একক একক ইউনিট হিসেবে সম্পাদিত হয়। একটি ট্রানজেকশনের মধ্যে বিভিন্ন কুয়েরি বা অপারেশন থাকতে পারে, কিন্তু এগুলি সকলের সফলভাবে সম্পন্ন হতে হবে অথবা সম্পূর্ণভাবে ব্যর্থ হতে হবে।

MySQL ট্রানজেকশন ব্যবস্থাপনা ACID (Atomicity, Consistency, Isolation, Durability) গুণাবলী অনুসরণ করে, যা ডেটাবেসের ডেটার অখণ্ডতা, সুরক্ষা এবং নির্ভরযোগ্যতা নিশ্চিত করে।

1. MySQL Transaction

MySQL এ ট্রানজেকশন শুরু করতে, আপনি START TRANSACTION অথবা BEGIN কমান্ড ব্যবহার করতে পারেন। একটি ট্রানজেকশন সম্পূর্ণ করার জন্য COMMIT এবং বাতিল করার জন্য ROLLBACK কমান্ড ব্যবহৃত হয়।

ট্রানজেকশন শুরু করা:

START TRANSACTION;
-- OR
BEGIN;

ট্রানজেকশন সম্পন্ন করা:

যদি আপনি নিশ্চিত হন যে সমস্ত অপারেশন সফল হয়েছে এবং আপনি ডেটাবেসে পরিবর্তনগুলো সংরক্ষণ করতে চান, তখন COMMIT ব্যবহার করা হয়:

COMMIT;

ট্রানজেকশন বাতিল করা:

যদি ট্রানজেকশনের মধ্যে কোনো সমস্যা ঘটে এবং আপনি সব পরিবর্তন বাতিল করতে চান, তখন ROLLBACK ব্যবহার করা হয়:

ROLLBACK;

ট্রানজেকশন চেক করা:

ট্রানজেকশনের অবস্থা চেক করতে SHOW ENGINE INNODB STATUS কমান্ড ব্যবহার করতে পারেন। এটি বর্তমান ট্রানজেকশন এবং লকিং সম্পর্কিত তথ্য প্রদর্শন করবে।

SHOW ENGINE INNODB STATUS;

2. ACID Properties

ACID হলো চারটি মৌলিক গুণাবলী যা ট্রানজেকশনকে নির্ভরযোগ্য এবং সুরক্ষিত করে তোলে। এর পূর্ণরূপ হলো:

• Atomicity (আটমিকিটি)
• Consistency (অংশীদারিতা)
• Isolation (বিচ্ছিন্নতা)
• Durability (স্থায়িত্ব)

1. Atomicity (আটমিকিটি)

Atomicity নিশ্চিত করে যে, একটি ট্রানজেকশনের সমস্ত অপারেশন একযোগে সফলভাবে সম্পন্ন হতে হবে অথবা কোনটিও সম্পন্ন হবে না। এর মানে হল, যদি ট্রানজেকশনের মধ্যে কোনো একটি অপারেশন ব্যর্থ হয়, তবে সমস্ত অপারেশন ব্যর্থ হবে এবং ডেটাবেস আগের অবস্থায় ফিরে যাবে।

যেমন:
যদি আপনি দুটি অ্যাকাউন্টে টাকা ট্রান্সফার করতে চান (একটি অ্যাকাউন্ট থেকে টাকা কাটা এবং অন্যটিতে যোগ করা), তবে যদি টাকা কাটা হয় কিন্তু অন্য অ্যাকাউন্টে যোগ না হয়, তাহলে আপনি একটি ব্যর্থ ট্রানজেকশন পাবেন। উভয় পরিবর্তনই বাতিল করা হবে।

2. Consistency (অংশীদারিতা)

Consistency নিশ্চিত করে যে, ট্রানজেকশন চালানোর পর ডেটাবেস সবসময় বৈধ অবস্থায় থাকবে। অর্থাৎ, ট্রানজেকশনের পূর্বে এবং পরে ডেটাবেসের সুশৃঙ্খলতা বজায় থাকবে।

যেমন:
ডেটাবেসের সব কনস্ট্রেইন্টস (যেমন, প্রাইমারি কি, ফোরেন কী, নাল কনস্ট্রেইন্ট) কার্যকর থাকবে এবং কোনো ট্রানজেকশন ডেটাবেসের অবস্থাকে সঠিকভাবে পরিবর্তন করবে, যাতে তা ডেটাবেসের সঠিক কাঠামো অনুসরণ করে।

3. Isolation (বিচ্ছিন্নতা)

Isolation নিশ্চিত করে যে, একাধিক ট্রানজেকশন চলতে থাকলে একটিও অপরটি প্রভাবিত করবে না। এর মানে হল যে, একাধিক ট্রানজেকশন একে অপরের কাজের সাথে হস্তক্ষেপ না করে পৃথকভাবে কার্যকর হবে।

MySQL ইনডিবি ইঞ্জিনে চারটি Isolation Level আছে:

• READ UNCOMMITTED: এক ট্রানজেকশন অন্য ট্রানজেকশনের অপর্যাপ্ত (uncommitted) ডেটাও দেখতে পারে।
• READ COMMITTED: এক ট্রানজেকশন শুধুমাত্র কমিট হওয়া ডেটা দেখতে পাবে।
• REPEATABLE READ: এক ট্রানজেকশন প্রথম কুয়েরি করার পর দ্বিতীয় কুয়েরির মধ্যে ডেটার কোনো পরিবর্তন হবে না।
• SERIALIZABLE: সর্বোচ্চ বিচ্ছিন্নতা, যেখানে একাধিক ট্রানজেকশন একে অপরের মধ্যে একে একে সম্পন্ন হবে, এক সময় কোনো দুটি ট্রানজেকশন একসাথে চলতে পারবে না।

4. Durability (স্থায়িত্ব)

Durability নিশ্চিত করে যে, একবার একটি ট্রানজেকশন COMMIT হয়ে গেলে, তার সমস্ত পরিবর্তন স্থায়ীভাবে ডেটাবেসে সংরক্ষিত হবে। এমনকি যদি সিস্টেম ক্র্যাশ হয়ে যায়, তবুও ডেটার পরিবর্তন হারিয়ে যাবে না। ডেটাবেস একটি স্থায়ী স্টেট বজায় রাখবে।

যেমন:
আপনি যদি একটি ট্রানজেকশন কমিট করেন এবং তারপর সিস্টেম ক্র্যাশ করে, তবে ট্রানজেকশনের পরিবর্তনগুলি পুনরুদ্ধার করা হবে এবং ডেটাবেস সঠিক অবস্থায় ফিরে আসবে।

সারাংশ

MySQL-এ ট্রানজেকশন ব্যবস্থাপনা একটি গুরুত্বপূর্ণ অংশ, যা ডেটাবেসে একাধিক অপারেশনকে একত্রিত করে একটি একক ইউনিটে সম্পন্ন করতে সাহায্য করে। ACID গুণাবলী নিশ্চিত করে যে, ট্রানজেকশনগুলো নিরাপদ, সুশৃঙ্খল এবং নির্ভরযোগ্য হবে। Atomicity, Consistency, Isolation, এবং Durability এই চারটি গুণাবলী মিলে ডেটাবেসের সম্পূর্ণ অখণ্ডতা ও সুরক্ষা নিশ্চিত করে, যা ডেটাবেসের সঠিক কার্যক্ষমতা বজায় রাখে।

একটি Transaction হল একটি সিরিজ বা গ্রুপ কার্যক্রম যা একক, অ atomic, অর্থাৎ সম্পূর্ণভাবে সফল হওয়া বা ব্যর্থ হওয়া উচিত। এটি সাধারণত ডেটাবেসে ডেটা আপডেট, ইনসার্ট, ডিলিট বা অন্যান্য কার্যক্রমকে একত্রে সামলানোর জন্য ব্যবহৃত হয়। একটি ট্রানজেকশন কার্যকরভাবে নিশ্চিত করে যে ডেটাবেসের প্রতিটি আপডেট বা পরিবর্তন পরিপূর্ণ এবং অখণ্ড (consistent) থাকবে।

Transaction এর মূল বৈশিষ্ট্য (ACID Properties)

একটি ডেটাবেস ট্রানজেকশনের জন্য ACID নামক ৪টি প্রধান বৈশিষ্ট্য রয়েছে, যা নিশ্চিত করে যে ট্রানজেকশনগুলি সঠিকভাবে কার্যকর হবে:

1. Atomicity (অ্যাটমিসিটি):
   • একটি ট্রানজেকশন হল একক একক ইউনিট। এটি পুরোপুরি সম্পন্ন হতে হবে অথবা সম্পূর্ণভাবে ব্যর্থ হতে হবে। যদি কোনো কারণে ট্রানজেকশন ব্যর্থ হয়, তবে সমস্ত পরিবর্তন পূর্বাবস্থায় ফিরে যাবে।
   • উদাহরণ: ব্যাংক ট্রানজেকশনের ক্ষেত্রে, যদি টাকা ট্রান্সফার করার সময় ব্যালেন্স হালনাগাদ না হয়, তবে পুরো ট্রানজেকশন বাতিল হয়ে যাবে এবং সমস্ত পরিবর্তন পূর্বাবস্থায় ফিরে যাবে।
2. Consistency (কনসিসটেন্সি):
   • ট্রানজেকশন একটি ডেটাবেসের কনসিসটেন্সি ধরে রাখবে। অর্থাৎ, ট্রানজেকশন শুরু এবং শেষ হওয়ার পরে ডেটাবেস সর্বদা বৈধ অবস্থায় থাকবে।
   • উদাহরণ: যদি একটি অ্যাকাউন্ট থেকে টাকা তোলা হয়, তবে তা অবশ্যই একটি অ্যাকাউন্ট থেকে কেটে যাবে এবং অন্য অ্যাকাউন্টে যোগ হবে।
3. Isolation (আইসোলেশন):
   • একটি ট্রানজেকশন সম্পূর্ণ হওয়ার আগ পর্যন্ত অন্যান্য ট্রানজেকশনগুলো সেটির সঙ্গে কোন ধরনের সম্পর্ক স্থাপন করতে পারে না। একাধিক ট্রানজেকশন একসঙ্গে সম্পন্ন হলে তাদের পারস্পরিক প্রভাব পড়বে না।
   • উদাহরণ: যদি দুটি ভিন্ন ট্রানজেকশন একে অপরের সাথে ডেটা আপডেট করে, তবে তারা একে অপরের মধ্যে হস্তক্ষেপ করবে না এবং তাদের নিজ নিজ কাজ সম্পন্ন করবে।
4. Durability (ডিউরেবিলিটি):
   • একবার একটি ট্রানজেকশন সফলভাবে সম্পন্ন হলে, তার পরিবর্তনগুলো স্থায়ী হবে এবং সিস্টেমের ক্র্যাশ বা অন্য কোনো ধরনের ব্যর্থতার পরেও তা সুরক্ষিত থাকবে।
   • উদাহরণ: যদি আপনি একটি ব্যাংক অ্যাকাউন্টে টাকা জমা দেন এবং ট্রানজেকশন সফলভাবে সম্পন্ন হয়, তবে সেই টাকা আগামীকাল পর্যন্ত অ্যাকাউন্টে থাকবে।

কেন Transaction প্রয়োজন?

1. ডেটাবেসে সঠিকতা এবং অখণ্ডতা রক্ষা:
   • ট্রানজেকশন ডেটাবেসে ডেটার অখণ্ডতা নিশ্চিত করে। যদি কোনো প্রক্রিয়া অসম্পূর্ণ থাকে বা ব্যর্থ হয়, তবে ট্রানজেকশনটি রোলব্যাক (rollback) করা হয়, যাতে ডেটাবেসের অবস্থা অক্ষত থাকে।
2. Multiple Operations Management:
   • একটি ট্রানজেকশন একাধিক ডেটাবেস অপারেশন পরিচালনা করতে সক্ষম। উদাহরণস্বরূপ, একটি ব্যাংক অ্যাকাউন্ট থেকে টাকা ট্রান্সফার করা হলে দুইটি অপারেশন (টাকা কাটা এবং টাকা যোগ করা) একসাথে সম্পন্ন হয়, যাতে কোনও একটি অপারেশন ব্যর্থ হলে পুরো ট্রানজেকশন ব্যর্থ হয়ে যায়।
3. বিরোধী প্রক্রিয়া বন্ধ করা (Concurrency Control):
   • একাধিক ব্যবহারকারী বা সিস্টেম একে অপরকে হস্তক্ষেপ না করে একই সময়ে ডেটাবেসে পরিবর্তন করতে পারে। ট্রানজেকশনগুলি বিভিন্ন অপারেশনের মধ্যে আইসোলেশন বজায় রাখে, যাতে একে অপরের উপর প্রভাব না ফেলে।
4. ডেটাবেস ব্যাকআপ এবং রিকভারি:
   • একবার সফলভাবে ট্রানজেকশন সম্পন্ন হলে, তার সমস্ত পরিবর্তন স্থায়ীভাবে ডেটাবেসে সংরক্ষিত থাকে। যদি কোনও কারণবশত সিস্টেম ক্র্যাশ হয়, তবে ট্রানজেকশনগুলি পুনরুদ্ধারের মাধ্যমে ডেটাবেসকে পূর্বের সঠিক অবস্থায় ফিরিয়ে আনা সম্ভব হয়।
5. ব্যবহারকারীর অভিজ্ঞতা উন্নত করা:
   • ট্রানজেকশন ব্যবহারকারীদের জন্য একটি সঠিক এবং নির্ভরযোগ্য ডেটাবেসে কাজ করার অভিজ্ঞতা তৈরি করে। ট্রানজেকশন ব্যবহারের মাধ্যমে ব্যবহারকারীরা নিশ্চিত হতে পারে যে, তাদের করা কোনো পরিবর্তন বা কাজ ডেটাবেসে সঠিকভাবে কার্যকর হবে।

ট্রানজেকশনের ব্যবহার উদাহরণ:

ব্যাংকিং ট্রানজেকশন (Banking Transaction)

ধরা যাক, আপনি একটি ব্যাংক অ্যাকাউন্টে টাকা ট্রান্সফার করতে চান। এই ক্ষেত্রে দুটি কাজের সমন্বয় দরকার:

1. প্রথমে, আপনার অ্যাকাউন্ট থেকে টাকা কেটে নিতে হবে।
2. তারপর, সেই টাকা গন্তব্য অ্যাকাউন্টে যোগ করতে হবে।

এই দুইটি কাজ একত্রে করা গেলে, সিস্টেম কোনও কারণে ব্যর্থ হলে, প্রথমটি সম্পন্ন হলেও দ্বিতীয়টি ব্যর্থ হতে পারে। তাই Transaction ব্যবহার করা হয়। যদি কোনও একটি অংশ ব্যর্থ হয়, তবে পুরো প্রক্রিয়া (রোলব্যাক) বাতিল হবে এবং কোন ভুল ডেটা তৈরি হবে না।

Transaction ব্যবহারের উদাহরণ

MySQL Transaction (ব্যবহারের উদাহরণ)

START TRANSACTION;

UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;

COMMIT;

এখানে, প্রথমে দুটি UPDATE কমান্ড একত্রে একটি ট্রানজেকশনের মধ্যে চলে আসে। যদি দুটি আপডেট সফলভাবে সম্পন্ন হয়, তবে COMMIT কমান্ডের মাধ্যমে পরিবর্তনগুলো ডেটাবেসে সেভ হয়ে যাবে। তবে, যদি কোনো কারণে এই ট্রানজেকশনটি ব্যর্থ হয়, তবে ROLLBACK কমান্ড ব্যবহার করে সিস্টেম পূর্বের অবস্থায় ফিরে যেতে পারবে।

START TRANSACTION;

UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;

-- যদি কোন সমস্যা হয়
ROLLBACK;

সারাংশ

ট্রানজেকশন একটি অত্যন্ত গুরুত্বপূর্ণ কনসেপ্ট, যা ডেটাবেসের অখণ্ডতা এবং সঠিকতা বজায় রাখতে সাহায্য করে। এটি একটি সিরিজ কাজের মধ্যে একে অপরের ওপর নির্ভরশীলতা তৈরি করে এবং নিশ্চিত করে যে সমস্ত কার্যক্রম একযোগে সফলভাবে বা ব্যর্থভাবে সম্পন্ন হবে। ACID বৈশিষ্ট্যগুলি ট্রানজেকশনকে নিরাপদ এবং নির্ভরযোগ্য করে তোলে, যার ফলে এটি ডেটাবেস ব্যবস্থাপনার জন্য অপরিহার্য হয়ে ওঠে।

MySQL এ COMMIT এবং ROLLBACK হল ট্রানজেকশন ম্যানেজমেন্ট কমান্ড। এগুলি ডেটাবেসের ডেটা পরিবর্তন করার পর, সেই পরিবর্তনগুলোকে নিশ্চিত বা বাতিল করার জন্য ব্যবহৃত হয়। সাধারণত, COMMIT এবং ROLLBACK একটি ট্রানজেকশনের অংশ হিসেবে কাজ করে।

1. COMMIT

COMMIT কমান্ডটি একটি ট্রানজেকশন সম্পন্ন এবং নিশ্চিত করার জন্য ব্যবহৃত হয়। এটি ডেটাবেসের সমস্ত পরিবর্তনগুলি স্থায়ী (permanent) করে তোলে এবং আপনি যদি ট্রানজেকশন সঠিকভাবে সম্পন্ন করতে চান, তবে COMMIT কমান্ডটি ব্যবহার করতে হবে।

সyntax:

COMMIT;

উদাহরণ:

ধরা যাক, আমাদের একটি employees টেবিল আছে এবং আমরা কিছু ডেটা আপডেট করতে চাই।

START TRANSACTION;  -- ট্রানজেকশন শুরু
UPDATE employees SET salary = 55000 WHERE employee_id = 1;
UPDATE employees SET salary = 48000 WHERE employee_id = 2;

-- ট্রানজেকশন শেষ এবং পরিবর্তনগুলো নিশ্চিত
COMMIT;

এখানে, প্রথমে START TRANSACTION দিয়ে ট্রানজেকশন শুরু হয়েছে, এবং তারপর দুটি UPDATE কমান্ডের মাধ্যমে ডেটাবেসে পরিবর্তন করা হয়েছে। COMMIT কমান্ডটি কল করার মাধ্যমে সমস্ত পরিবর্তন সেভ হয়ে যাবে এবং ডেটাবেসে স্থায়ী হবে।

2. ROLLBACK

ROLLBACK কমান্ডটি একটি ট্রানজেকশন বাতিল (undo) করার জন্য ব্যবহৃত হয়। এটি সেই সমস্ত পরিবর্তনগুলিকে বাতিল করে দেয় যা ট্রানজেকশনের মধ্যে হয়েছিল, এবং ডেটাবেস পূর্বের অবস্থায় ফিরে যায়। যদি কোনও ত্রুটি (error) ঘটে এবং আপনি পরিবর্তনগুলি বাতিল করতে চান, তবে ROLLBACK ব্যবহার করা হয়।

সsyntax:

ROLLBACK;

উদাহরণ:

ধরা যাক, আমরা একই employees টেবিল ব্যবহার করছি এবং একটি ত্রুটি (error) হওয়ার পরে ডেটা রোলব্যাক করতে চাই।

START TRANSACTION;  -- ট্রানজেকশন শুরু
UPDATE employees SET salary = 55000 WHERE employee_id = 1;
UPDATE employees SET salary = 48000 WHERE employee_id = 2;

-- কিছু সমস্যা ঘটল, তাই পরিবর্তন বাতিল করা হল
ROLLBACK;

এখানে, START TRANSACTION দিয়ে ট্রানজেকশন শুরু হয়েছে এবং দুটি UPDATE কমান্ডের মাধ্যমে পরিবর্তন করা হয়েছে। কিন্তু যদি কোনো কারণে কিছু ভুল হয়ে যায় বা আপনি পরিবর্তনগুলি বাতিল করতে চান, তাহলে ROLLBACK কমান্ডটি কল করে সমস্ত পরিবর্তন ফিরিয়ে নেয়া হবে।

3. SAVEPOINT (Optional)

একটি ট্রানজেকশনের মধ্যে একাধিক পদক্ষেপের পর ROLLBACK বা COMMIT এর মধ্যে শুধুমাত্র একটি নির্দিষ্ট পয়েন্ট পর্যন্ত পরিবর্তনগুলো ফেরত নেয়ার জন্য SAVEPOINT ব্যবহার করা হয়। এর মাধ্যমে আপনি একটি ট্রানজেকশনকে ছোট ছোট অংশে ভাগ করতে পারেন এবং একে একে কন্ট্রোল করতে পারেন।

সyntax:

SAVEPOINT savepoint_name;

উদাহরণ:

START TRANSACTION;

-- প্রথম UPDATE
UPDATE employees SET salary = 55000 WHERE employee_id = 1;

-- SAVEPOINT তৈরি করা হল
SAVEPOINT update1;

-- দ্বিতীয় UPDATE
UPDATE employees SET salary = 48000 WHERE employee_id = 2;

-- যদি দ্বিতীয় UPDATE এর পর সমস্যা হয়, তাহলে প্রথমটি রোলব্যাক করতে হবে
ROLLBACK TO update1;

COMMIT;

এখানে, SAVEPOINT কমান্ড ব্যবহার করে আপনি update1 পয়েন্টটি সংরক্ষণ করেছেন। এর পরে, যদি দ্বিতীয় UPDATE এর পর কোনো সমস্যা হয়, তাহলে আপনি সেই নির্দিষ্ট পয়েন্টে ফিরে যেতে (rollback) পারেন এবং শুধুমাত্র পরবর্তী পরিবর্তনগুলো বাতিল করতে পারেন।

সারাংশ

• COMMIT: এটি ট্রানজেকশন শেষ এবং সমস্ত পরিবর্তন স্থায়ী (permanent) করার জন্য ব্যবহৃত হয়।
• ROLLBACK: এটি ট্রানজেকশন বাতিল করার জন্য ব্যবহৃত হয়, যা সমস্ত পরিবর্তন পূর্ববর্তী অবস্থায় ফিরিয়ে আনে।
• SAVEPOINT: এটি ট্রানজেকশনের মধ্যে একটি নির্দিষ্ট পয়েন্ট সংরক্ষণ করে এবং যদি প্রয়োজন হয়, পরে শুধুমাত্র সেই পয়েন্ট পর্যন্ত পরিবর্তন ফিরিয়ে নেওয়া যায়।

এই কমান্ডগুলো ডেটাবেসের ডেটাকে নিরাপদ রাখতে এবং ত্রুটির ক্ষেত্রে সহজেই ব্যাকআপ নেয়ার সুযোগ প্রদান করে।

Transaction Isolation Levels (ট্রানজেকশন আইসোলেশন লেভেলস) হল একটি গুরুত্বপূর্ণ ধারণা, যা ACID (Atomicity, Consistency, Isolation, Durability) বৈশিষ্ট্য নিশ্চিত করতে সাহায্য করে। Isolation একটি ট্রানজেকশনের ডেটা অন্য ট্রানজেকশনের প্রভাব থেকে কতটা বিচ্ছিন্ন থাকবে, তা নির্ধারণ করে। MySQL-এ বিভিন্ন ট্রানজেকশন আইসোলেশন লেভেল রয়েছে, প্রতিটি লেভেল বিভিন্ন ধরনের concurrency এবং data consistency সমস্যা সমাধান করতে সাহায্য করে।

MySQL-এ মোট চারটি ট্রানজেকশন আইসোলেশন লেভেল আছে:

1. Read Uncommitted
2. Read Committed
3. Repeatable Read
4. Serializable

প্রত্যেকটি লেভেল ডেটা অ্যাক্সেসের আচরণ এবং প্রতিযোগিতামূলক ট্রানজেকশনগুলির (concurrent transactions) কিভাবে পরিচালিত হবে তা নির্ধারণ করে।

1. Read Uncommitted (সর্বনিম্ন আইসোলেশন)

• Description: এই লেভেলে, একটি ট্রানজেকশন অন্য ট্রানজেকশনের অপরিবর্তিত ডেটা (uncommitted data) দেখতে পারে। এটি dirty reads (অথবা অপরিষ্কার ডেটা) এর অনুমতি দেয়।
• Problem: ট্রানজেকশনটি যে ডেটা দেখতে পাচ্ছে তা অন্য ট্রানজেকশনে এখনও কমিট হয়নি, এবং সে ডেটা পরে অন্য ট্রানজেকশনে রোলব্যাক হতে পারে। এর ফলে অবিশ্বাস্য বা ভুল ফলাফল পাওয়া যেতে পারে।
• Example: যদি একটি ট্রানজেকশন কোনো তথ্য আপডেট করে কিন্তু কমিট না করে, তবে অন্য ট্রানজেকশন সেই অপরিবর্তিত ডেটা দেখতে পাবে। পরে প্রথম ট্রানজেকশনটি রোলব্যাক করলে, দ্বিতীয় ট্রানজেকশনে ব্যবহৃত ডেটা অকার্যকর হয়ে যাবে।

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

2. Read Committed

• Description: এই লেভেলে, একটি ট্রানজেকশন শুধুমাত্র অন্য ট্রানজেকশনের কমিটেড ডেটা দেখতে পায়। অর্থাৎ, "dirty reads" প্রতিরোধ করা হয়। তবে non-repeatable reads (যেখানে একই ট্রানজেকশন দুটি আলাদা সময়ের মধ্যে একে অপরের সাথে ভিন্ন ডেটা দেখে) এখনও সম্ভব।
• Problem: ট্রানজেকশনটি যেই ডেটা পড়ছে, তা অন্য ট্রানজেকশনের দ্বারা আপডেট হতে পারে যখন ট্রানজেকশনটি চলছে, যা non-repeatable reads সৃষ্টি করে।
• Example: যদি প্রথম ট্রানজেকশন একটি ডেটা পড়বে এবং তারপর দ্বিতীয় ট্রানজেকশন সেই ডেটা আপডেট করবে, তবে প্রথম ট্রানজেকশন যখন আবার সেই ডেটা পড়বে, এটি আগের মানের সাথে মেলাবে না।

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

3. Repeatable Read

• Description: এই লেভেলে, একটি ট্রানজেকশন একই ডেটাকে বার বার পড়তে পারে এবং প্রতি বার একই ফলাফল পাবে। এটি dirty reads এবং non-repeatable reads দুটিই প্রতিরোধ করে। তবে phantom reads (যেখানে একে অপরের সাথে মিলে না এমন নতুন রেকর্ডগুলো একটি ট্রানজেকশনের ভেতরে হাজির হয়) হতে পারে।
• Problem: যদিও ডেটা পুনরায় পড়লে আগের মতোই থাকবে, তবে অন্য ট্রানজেকশনের মাধ্যমে নতুন রেকর্ড তৈরি হতে পারে, যেটি এই ট্রানজেকশনটি পরে দেখবে না।
• Example: একটি ট্রানজেকশন যখন কোনো ডেটা আপডেট বা পড়তে থাকে, তখন সেই ডেটা অন্য ট্রানজেকশন দ্বারা পরিবর্তিত হতে পারে না, তবে নতুন রেকর্ড বা ডেটা সেই ট্রানজেকশনটি পড়বে না।

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

4. Serializable (সর্বোচ্চ আইসোলেশন)

• Description: এটি সবচেয়ে শক্তিশালী আইসোলেশন লেভেল, যেখানে প্রতিটি ট্রানজেকশন একে অপর থেকে পুরোপুরি বিচ্ছিন্ন থাকে। ট্রানজেকশনগুলি একে অপরের সাথে সিরিয়ালি (যেমন একে একে) চলবে, যা dirty reads, non-repeatable reads, এবং phantom reads সব ধরনের সমস্যা দূর করে।
• Problem: এটি সিস্টেমের পারফরমেন্সে প্রভাব ফেলতে পারে কারণ সমস্ত ট্রানজেকশন একে অপরের পরে আসবে, এবং concurrency এর সুবিধা কমে যাবে।
• Example: যদি দুটি ট্রানজেকশন একই ডেটার উপর কাজ করার চেষ্টা করে, তবে একটি ট্রানজেকশন শেষ না হওয়া পর্যন্ত অন্যটি অপেক্ষা করবে।

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Comparison of Transaction Isolation Levels

• Dirty Reads: যখন একটি ট্রানজেকশন অপরিপূর্ণ (uncommitted) ডেটা পড়তে পারে।
• Non-Repeatable Reads: যখন একটি ট্রানজেকশন পুনরায় পড়া ডেটা পরিবর্তন হতে পারে অন্য ট্রানজেকশনের মাধ্যমে।
• Phantom Reads: যখন একে অপরের সাথে মিলিত না হওয়া নতুন রেকর্ডগুলো একে একে প্রবাহিত হয়।

Choosing the Right Isolation Level

1. Read Uncommitted: এটি low latency এবং high throughput প্রয়োজনের ক্ষেত্রে ব্যবহার করা যেতে পারে, তবে খুব কম নিরাপত্তা বা নির্ভুলতা নিশ্চিত করা হয়।
2. Read Committed: যখন আপনি dirty reads থেকে বাঁচতে চান, তবে ডেটা একসাথে পড়া এবং আপডেট হওয়া দরকার না হলে এই লেভেল উপযুক্ত।
3. Repeatable Read: যদি আপনি ডেটার অভ্যন্তরীণConsistency এবং নির্ভুলতা চান, এবং আপনি চান যে কোনো ট্রানজেকশন পুনরায় একই ডেটা পড়ুক, তবে এই লেভেল উপযুক্ত।
4. Serializable: সর্বোচ্চ নির্ভুলতা এবং সম্পূর্ণ সুরক্ষা চাইলে এবং concurrency সমস্যা পরিহার করতে চাইলে, তবে এই লেভেলই সেরা।

সারাংশ

MySQL-এ transaction isolation levels এর মাধ্যমে ট্রানজেকশনগুলির মধ্যে data consistency এবং concurrency সমস্যাগুলি নিয়ন্ত্রণ করা হয়। প্রতিটি লেভেল পারফরমেন্স এবং ডেটা সুরক্ষার মধ্যে একটি ব্যালেন্স তৈরি করে, এবং আপনার প্রজেক্টের প্রয়োজন অনুযায়ী সঠিক লেভেল বেছে নেওয়া গুরুত্বপূর্ণ।

Deadlock Management এবং Concurrency Control হল ডেটাবেস পরিচালনার দুটি অত্যন্ত গুরুত্বপূর্ণ ধারণা যা ডেটাবেসের কার্যক্ষমতা এবং নির্ভরযোগ্যতা নিশ্চিত করতে সাহায্য করে। এগুলি মূলত সিস্টেমের পারফরমেন্স এবং ডেটার সুরক্ষা নিশ্চিত করতে ব্যবহৃত হয়, বিশেষ করে যখন একাধিক ইউজার বা ট্রানজেকশন একসাথে ডেটাবেসে কাজ করছে।

এখানে আমরা Deadlock Management এবং Concurrency Control এর বিস্তারিত আলোচনা করব।

1. Deadlock Management

Deadlock হল একটি পরিস্থিতি যেখানে দুটি বা তার বেশি ট্রানজেকশন একে অপরের জন্য অপেক্ষা করছে, এবং তারা কখনও শেষ হবে না। এটি মূলত তখন ঘটে যখন একাধিক ট্রানজেকশন একই রিসোর্সের উপর লক নেয়, এবং প্রতিটি ট্রানজেকশন অন্যটির জন্য অপেক্ষা করছে। ফলস্বরূপ, সিস্টেমটি স্থির হয়ে যায় এবং কোনো ট্রানজেকশনই সম্পন্ন হতে পারে না।

Deadlock উদাহরণ

ধরা যাক, দুটি ট্রানজেকশন (T1 এবং T2) একই সময় একাধিক টেবিলের উপর কাজ করছে:

• T1: Table1-এ একটি রেকর্ড আপডেট করছে এবং একই সময়ে T2 Table2-এ একটি রেকর্ড আপডেট করছে।
• T2: এখন Table1-এর উপর লক চাইছে এবং T1 Table2-এর উপর লক চাইছে।

এভাবে, দুটি ট্রানজেকশন একে অপরের জন্য অপেক্ষা করতে থাকে এবং কোনোটিই সম্পন্ন হতে পারে না, এটি Deadlock তৈরি করে।

Deadlock Detection and Handling in MySQL

MySQL Deadlock এর মোকাবিলা করার জন্য বিভিন্ন পদ্ধতি ব্যবহার করে। InnoDB স্টোরেজ ইঞ্জিন নিজেই Deadlock সনাক্ত এবং পরিচালনা করতে সক্ষম।

• Deadlock সনাক্তকরণ: MySQL স্বয়ংক্রিয়ভাবে Deadlock সনাক্ত করে এবং যদি এটি হয়, তবে একটি ট্রানজেকশনকে ROLLBACK করে দেয়। সাধারণত, যেই ট্রানজেকশন কম কাজ করেছে সেটি রোলব্যাক করা হয়।
• Error Code: Deadlock ঘটে গেলে MySQL 1213 - Deadlock found when trying to get lock ত্রুটি কোড দিয়ে জানায়।

Deadlock Prevention

Deadlock প্রতিরোধ করার জন্য কিছু পদ্ধতি গ্রহণ করা যেতে পারে:

1. একই রিসোর্স অর্ডার অনুসরণ করা: সমস্ত ট্রানজেকশন একই রিসোর্সের উপর লক নেয়ার জন্য একটি নির্দিষ্ট অর্ডার অনুসরণ করলে Deadlock কমানো যায়।
2. ট্রানজেকশন সংক্ষিপ্ত রাখা: ট্রানজেকশন যতটা সম্ভব ছোট এবং দ্রুত রাখা উচিত।
3. লক টাইমআউট নির্ধারণ: লক সময় সীমা নির্ধারণ করা যেতে পারে, যাতে দীর্ঘ সময় অপেক্ষা করলে ট্রানজেকশন স্বয়ংক্রিয়ভাবে বাতিল হয়ে যায়।

2. Concurrency Control

Concurrency Control হল একটি প্রক্রিয়া যার মাধ্যমে একাধিক ট্রানজেকশনকে সমান্তরালভাবে সঞ্চালন করা হয় এবং ডেটাবেসের অখণ্ডতা বজায় রাখা হয়। এটি নিশ্চিত করে যে একাধিক ট্রানজেকশন একই ডেটার উপর একযোগভাবে কাজ করার সময় ডেটার সামঞ্জস্যপূর্ণতা এবং সঠিকতা বজায় থাকে।

Concurrency Control এর উদ্দেশ্য

1. Data Integrity: নিশ্চিত করা যে সমান্তরালভাবে কাজ করা ট্রানজেকশনগুলো ডেটা কনসিস্টেন্সি বজায় রাখবে।
2. Isolation: ট্রানজেকশনগুলির মধ্যে কোনো একে অপরের কাজের ব্যাঘাত না ঘটানো।
3. Consistency: প্রতিটি ট্রানজেকশন সঠিকভাবে সম্পন্ন হবে, যাতে ডেটাবেসের কোনো ভুল বা অবৈধ অবস্থা না হয়।

Concurrency Control Mechanisms

MySQL এ Concurrency Control এর জন্য প্রধানত Locking Mechanism এবং Transaction Isolation Levels ব্যবহৃত হয়।

1. Locking Mechanism (লকিং)
   • Row-level Locking (রো-লেভেল লকিং): ইনোডিবি (InnoDB) স্টোরেজ ইঞ্জিনে, যখন একটি ট্রানজেকশন একটি নির্দিষ্ট রেকর্ডের উপর কাজ করে, তখন শুধুমাত্র সেই রেকর্ডের জন্য লক করা হয় এবং অন্যান্য ট্রানজেকশন ঐ রেকর্ডটি অ্যাক্সেস করতে পারে না।
   • Table-level Locking (টেবিল-লেভেল লকিং): এই ক্ষেত্রে, পুরো টেবিলই লক হয়ে যায়, অর্থাৎ এক ট্রানজেকশন একটি টেবিলের উপর কাজ করলে অন্য ট্রানজেকশন সেই টেবিলের কোনো রেকর্ডই অ্যাক্সেস করতে পারে না।

2. Transaction Isolation Levels (আইসোলেশন লেভেল)

   Transaction Isolation Levels হল ট্রানজেকশনগুলির মধ্যে একে অপরের কাজের উপর কীভাবে প্রভাব পড়বে তা নির্ধারণকারী স্তর। MySQL-এ চারটি প্রধান isolation levels রয়েছে:

   • READ UNCOMMITTED: সবচেয়ে কম আইসোলেশন লেভেল, যেখানে একটি ট্রানজেকশন অপর ট্রানজেকশন থেকে অপ্রকাশিত (uncommitted) পরিবর্তনও দেখতে পায়। এটি dirty read অনুমোদন করে।
   • READ COMMITTED: ট্রানজেকশন শুধুমাত্র অন্যান্য কমিট হওয়া ট্রানজেকশনগুলির পরিবর্তন দেখতে পায়। এটি non-repeatable reads অনুমোদন করে, অর্থাৎ একে অপরের মধ্যে মান পরিবর্তিত হতে পারে।
   • REPEATABLE READ: MySQL এর ডিফল্ট আইসোলেশন লেভেল, যেখানে একই ট্রানজেকশনের মধ্যে একটি রেকর্ড একাধিকবার পড়া হলে তার মান পরিবর্তিত হবে না, তবে phantom reads ঘটতে পারে।
   • SERIALIZABLE: সবচেয়ে উচ্চ আইসোলেশন লেভেল, যেখানে সব ট্রানজেকশন একে অপরের সাথে পুরোপুরি সিকোয়েন্সিয়ালভাবে চলবে। এতে phantom reads প্রতিরোধ করা হয়, কিন্তু এর পারফরমেন্স কমে যেতে পারে।

Locking and Isolation Example

-- Set transaction isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Start Transaction 1
START TRANSACTION;
UPDATE products SET stock = stock - 1 WHERE product_id = 1;

-- Start Transaction 2 (won't be able to update the same record until Transaction 1 is committed)
START TRANSACTION;
UPDATE products SET stock = stock - 1 WHERE product_id = 1;

এখানে, Transaction 2 তখন পর্যন্ত product_id = 1 এর উপর আপডেট করতে পারবে না, যতক্ষণ না Transaction 1 কমিট হয়, কারণ SERIALIZABLE আইসোলেশন লেভেলটি নিশ্চিত করে যে দুইটি ট্রানজেকশন একই রেকর্ডে একে অপরের মাধ্যমে কাজ করতে পারে না।

Concurrency Control এবং Deadlock Management এর সম্পর্ক

Deadlock এবং Concurrency Control একে অপরের সাথে সম্পর্কিত। যখন একাধিক ট্রানজেকশন সমান্তরালে কাজ করছে, তখন Deadlock তৈরি হতে পারে যদি একটি ট্রানজেকশন অন্যটিকে প্রতিরোধ করে। Concurrency Control নিশ্চিত করে যে ট্রানজেকশনগুলো সঠিকভাবে একে অপরের সাথে কাজ করবে এবং Deadlock এড়ানোর জন্য বিভিন্ন পদ্ধতি এবং লকিং কৌশল ব্যবহার করা হবে।

সারাংশ

• Deadlock Management হল একটি প্রক্রিয়া যা ডেটাবেসে Deadlock সনাক্ত করে এবং প্রয়োজনীয় ট্রানজেকশন রোলব্যাক করে। এর মাধ্যমে সিস্টেমের কার্যক্ষমতা এবং পারফরমেন্স বজায় রাখা যায়।
• Concurrency Control হল একটি পদ্ধতি যা একাধিক ট্রানজেকশনকে একই ডেটাবেসে সমান্তরালে কাজ করতে অনুমতি দেয়, তবে এটি নিশ্চিত করে যে ডেটার অখণ্ডতা বজায় থাকে। Locking এবং Transaction Isolation Levels এর মাধ্যমে কনকারেন্সি কন্ট্রোল পরিচালিত হয়।

Deadlock এবং Concurrency Control উভয়ই ডেটাবেস সিস্টেমের সঠিকতা, কার্যক্ষমতা এবং ডেটার নিরাপত্তা নিশ্চিত করতে অপরিহার্য।